package com.gwt.tirso.gymkhana.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.google.gwt.user.client.rpc.IsSerializable;
import com.gwt.tirso.gymkhana.client.Question;

public class JDBCQuestionDAO implements IQuestionDAO, IsSerializable {
	
	private Connection c;
	
	public JDBCQuestionDAO() {
	       c = ConnectionManager.getInstance().checkOut();
	}
	
	protected void finalize() {
	       ConnectionManager.getInstance().checkIn(c);
	}

	@Override
	public void insertQuestion(Question q) {
		PreparedStatement stmt = null;
		String sql = "INSERT INTO Questions ( id, num, questiontext, answertext, cluetext, code, isinitial) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try {
            stmt = c.prepareStatement(sql);
            //stmt.setString(1, q.getId());
            stmt.setLong(2, q.getNum());
            stmt.setString(3, q.getQuestionText());
            stmt.setString(4, q.getAnswerText());
            stmt.setString(5, q.getClueText());
            stmt.setString(6, q.getCode());
            stmt.setBoolean(7, q.isInitial());
            
            stmt.executeUpdate();

	        } catch (SQLException e) {
	            System.out.println("Message: insertQuestion: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
	    }
	}

	@Override
	public Question selectQuestionById(String idQuest) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		Question q = new Question();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Questions WHERE id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idQuest);

			stmt.executeQuery();
			result = stmt.executeQuery();

			result.next();
			
			//q.setId(result.getString("id"));
			q.setQuestionText(result.getString("questiontext"));
			q.setAnswerText(result.getString("answertext"));
			q.setClueText(result.getString("cluetext"));
			q.setCode(result.getString("code"));
			q.setInitial(result.getBoolean("isinitial"));
		

		} catch (SQLException e) {
			System.out.println("Message: selectGymkhanaByNameAndPass: "
					+ e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return q;
	}
}
